/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package net.vu.demo.bean;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import net.vu.demo.utils.DataUtils;

/**
 *
 * @author VUHOLMES
 */
public class EmployeeService {
    private ArrayList<Employee> employeeList; 
    private String connect_str = "jdbc:postgresql://localhost:5432/mydb";
    private String userid = "postgres";
    private String password = "tv09914032";
    
    public ArrayList<Employee> list(){ 
        return employeeList; 
    } 
    
    public void addEmployeeToList(){
        //System.out.println("addEmployeeToList() in Employee Action...."); 
        ResultSet rs = null;
        int i=0; 
        PreparedStatement pstmt = null;
        //System.out.println("done.......");
        try{
            DataUtils.connectDb(connect_str, userid, password);
            pstmt = DataUtils.getConnect().prepareStatement("SELECT * FROM struts_test.tblemployee"); 
            //rs= DataUtils.Doc("SELECT * FROM struts_test.tblemployee"); // connect ????
            rs = pstmt.executeQuery();
            
            /*String query = "SELECT * FROM struts_test.tblemployee";
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/struts_test", "root", "tv09914032");
            Statement stmt = con.createStatement(); 
            rs= stmt.executeQuery(query);*/
            
//            System.out.println("done1.......");
//            System.out.println(rs.toString());
//            
//            while (rs.next()) {
//  		      	String entry = rs.getString("name");
//  		      	System.out.println(entry);
//  		  	}
            
            employeeList = new ArrayList<Employee>();
            while(rs.next()){
                i++;
                Employee employee = new Employee();
                employee.setId(rs.getInt("ID")); 
                employee.setName(rs.getString("name")); 
                employee.setSex(rs.getString("sex")); 
                employee.setBirthday(rs.getString("birthday")); 
                employee.setCity(rs.getString("city")); 
                employee.setEmail(rs.getString("email")); 
                employee.setHistory(rs.getString("history")); 
                employeeList.add(employee);
                
//                System.out.print(employee.getId() +" " + employee.getName() +" " + employee.getSex());
//                System.out.println();
            }
        }
        catch(Exception e){
            //e.printStackTrace();
        }
        System.out.println(i);
    }
    
    public int addEmployee(Employee employee){ 
        System.out.println("addEmployee method in EmployeeService class"); 
        int ret = -1; 
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            DataUtils.connectDb(connect_str, userid, password);
            pstmt = DataUtils.getConnect().prepareStatement
                    ("INSERT INTO struts_test.tblemployee (ID, Name, Sex, Birthday, City, Email, History)VALUES(?, ?, ?, ?, ?, ?, ?)");
            
            rs= DataUtils.Doc("SELECT MAX(id) FROM struts_test.tblemployee");
            rs.next();
            int rowCount = rs.getInt(1);
            System.out.println(rowCount);
            
            pstmt.setInt(1, ++rowCount);
            pstmt.setString(2, employee.getName()); 
            pstmt.setString(3, employee.getSex()); 
            pstmt.setString(4, employee.getBirthday()); 
            pstmt.setString(5, employee.getCity()); 
            pstmt.setString(6, employee.getEmail()); 
            pstmt.setString(7, employee.getHistory()); 
            int result = pstmt.executeUpdate();
            if(result > 0){
                System.out.println("Employee insert successfully"); 
                ret = 1;
            }
            else{
                ret = 0;
            }
            
        }
        catch(Exception e){
            e.printStackTrace();
        }
        finally{
            if(pstmt != null) 
            { 
                try { 
                    pstmt.close(); 
                } 
                catch (SQLException e) { 
                    e.printStackTrace(); 
                } 
            } 
            DataUtils.disconnectDb(); 
        }
        return ret;
    }
    
    public int updateEmployee(Employee employee){ 
        System.out.println("updateEmployee method in EmployeeService class"); 
        int ret = -1; 
        PreparedStatement pstmt = null;
        try {
            DataUtils.connectDb(connect_str, userid, password);
            pstmt = DataUtils.getConnect().prepareStatement
                    ("UPDATE struts_test.tblemployee SET Name=?, Sex=?, Birthday=?, City=?, Email=?, History=? WHERE ID=?"); 
            pstmt.setString(1, employee.getName()); 
            pstmt.setString(2, employee.getSex()); 
            pstmt.setString(3, employee.getBirthday()); 
            pstmt.setString(4, employee.getCity()); 
            pstmt.setString(5, employee.getEmail()); 
            pstmt.setString(6,  employee.getHistory()); 
            pstmt.setInt(7, employee.getId());
            
            int result = pstmt.executeUpdate();
            if(result > 0){
                System.out.println("Employee saved successfully"); 
                ret = 1;
            }
            else{
                ret = 0;
            }
            
        }
        catch(Exception e){
            e.printStackTrace();
        }
        finally{
            if(pstmt != null) 
            { 
                try { 
                    pstmt.close(); 
                } 
                catch (SQLException e) { 
                    e.printStackTrace(); 
                } 
            } 
            DataUtils.disconnectDb(); 
        }
        return ret;
    }
    
    public int deleteEmployee(int id){ 
        System.out.println("deleteEmployee method in EmployeeService class"); 
        int ret = -1;
        PreparedStatement pstmt = null; 
        try {
            DataUtils.connectDb(connect_str, userid, password); 
            pstmt = DataUtils.getConnect().prepareStatement("DELETE FROM struts_test.tblemployee WHERE id=?"); 
            pstmt.setInt(1, id); 
            pstmt.executeUpdate(); 
            ret = 1;
        }
        catch (Exception e) { 
            e.printStackTrace(); 
        }
        finally{
            if(pstmt != null) 
            { 
                try { 
                    pstmt.close(); 
                } 
                catch (SQLException e) { 
                    e.printStackTrace(); 
                } 
            } 
            DataUtils.disconnectDb();
        }
        return ret;
    }
    
    public Employee findById(int id){
        addEmployeeToList(); 
        System.out.println("findById method in EmployeeService class"); 
        for (Employee employee : employeeList) { 
            if(employee.getId()==id) 
            return employee;
        }
        return null;
    }
    
    public Employee getEmployee(int id){
    	//System.out.println("addddddddd");
        Employee employee = new Employee(); 
        ResultSet rs = null; 
        PreparedStatement pstmt = null;
        try {
            DataUtils.connectDb(connect_str, userid, password); 
            pstmt = DataUtils.getConnect().prepareStatement("SELECT * FROM struts_test.tblemployee WHERE id=?"); 
            pstmt.setInt(1, id); 
            rs = pstmt.executeQuery(); 
        	//rs= DataUtils.Doc("SELECT * FROM struts_test.tblemployee");
            while (rs.next()) {
                employee.setId(rs.getInt("ID")); 
                employee.setName(rs.getString("Name")); 
                employee.setSex(rs.getString("Sex")); 
                employee.setBirthday(rs.getString("Birthday")); 
                employee.setCity(rs.getString("City")); 
                employee.setEmail(rs.getString("Email")); 
                employee.setHistory(rs.getString("History")); 
            }
        }
        catch (Exception e) { 
            e.printStackTrace(); 
        }
        finally 
        { 
            if(pstmt != null){
                try {
                    pstmt.close(); 
                } 
                catch (SQLException e) { 
                    e.printStackTrace(); 
                }
            } 
            if(rs != null){ 
            //rs.close(); 
            } 
            DataUtils.disconnectDb(); 
        }
        return employee;
    }
}
